<?php
/**
 * Created by PhpStorm.
 * User: JSQ-SL
 * Date: 2018/10/19
 * Time: 16:16
 */

namespace app\index\table;

use app\index\controller\Login;
use app\index\service\ExcelService;
use think\Db;

class ConFurnTable extends BaseTable
{
    protected static $table = 'CON_FURN';
    protected static $pk = 'FURN_NO';
    protected static $configTable = 'CON_FURN_CONFIG';

    static public function table(){
        return Db::table(static::$table.' f')
            ->leftJoin('DICT_FURN_FACITYPE df','df.FACITYPE_ID=f.FURNTYPE_NO')
            ->leftJoin('DICT_SB_DEPARTMENT dp','dp.depart_id=f.depart_id')
            ->leftJoin('DICT_COLLEGE dc','dc.COLLEGE_ID=dp.DEPART_BELONG')
            ->leftJoin('DICT_COUNTRY dcy','dcy.COUNTRY_ID=f.COUNTRY_ID')
            ->leftJoin('FURN_DICT_STATE ds','ds.STATE_ID=f.STATE_ID')
            ->leftJoin('EMPLOYEE emp','emp.STAFF_ID=f.INPUT_PERSON')
            ->leftJoin('EMPLOYEE emp2','emp2.STAFF_ID=f.FIXED_USER_ID')
            ->leftJoin('FURN_DICT_GB gb','gb.GBCODE=f.GB')
            ->leftJoin('DICT_FURN_ZIJIN zj','zj.ID=f.ZJLY')
            ->leftJoin('ASSET_DICT_BELONG bl','bl.BELONG_ID=f.BELONG_ID')
            ->leftJoin('ASSET_DICT_TYPE94TO2010 dt','dt.CCODE94=f.ASSET_TYPE_ID')
            ->leftJoin('CONSTRUCTION_INFO con','con.CONS_ID=f.CONS_ID')
            ->field([
                'f.FURN_NO'
                ,'f.FURN_NAME'
                ,"to_char(f.ENTER_TIME,'yyyy-mm-dd') ENTER_TIME"
                ,'FURNTYPE_NO'
                ,"df.FACITYPE"
                ,'f.DEPART_ID'
                ,"dp.DEPART_SHORT"
                ,'dc.COLLEGE_ID'
                ,'dc.SHORTNAME'
                ,'f.SPEC','f.MODEL','f.PRICE','f.FACTORY'
                ,"f.COUNTRY_ID"
                ,"dcy.COUNTRY_CNNAME"
                ,"f.FACTORY_NO"
                ,"to_char(f.FACTORY_DATE,'yyyy-mm-dd') FACTORY_DATE"
                ,"to_char(f.GET_DATE,'yyyy-mm-dd') GET_DATE"
                ,"ds.STATE","f.STATE_ID"
                ,"f.GET_PERSON","f.CHECK_PERSON","f.CHARGE_PERSON",'f.REMARK','f.INPUT_PERSON'
                ,"emp.NAME INPUT_NAME"
                ,"to_char(f.INPUT_TIME,'yyyy-mm-dd') INPUT_TIME"
                ,'f.GB'
                ,"gb.NAME GB_NAME"
                ,'f.ZJLY'
                ,"zj.ZIJIN"
                ,"f.ZJLY_DETAIL"
                ,"f.BELONG_ID"
                ,"bl.BELONG"
                ,'f.ASSET_TYPE_ID'
                ,"dt.CNAME94 ASSET_TYPE"
                ,"f.BRAND"
                ,"f.PLACE"
                ,"f.REMARK2"
                ,"f.ID"
                ,"f.NOTICE_NO"
                ,'f.MUTI_BUY_MARK'
                ,"f.SUBMIT_MARK"
                ,'f.CONS_ID'
                ,"f.IS_FIXED"
                ,"f.FIXED_NO"
                ,"to_char(f.FIXED_DATE,'yyyy-mm-dd') FIXED_DATE"
                ,"f.FIXED_USER_ID"
                ,'emp2.NAME FIXED_USER_NAME'
                ,'con.CONS_NO'
                ,'con.CONS_NAME'
            ]);

    }

    static public function add($data){
        $data['FURN_NO'] = static::getNextId();
        $data['INPUT_PERSON'] = Login::getUserId();
        $data['INPUT_TIME'] = date('Y-m-d');

        self::toDate($data,'ENTER_TIME');
        self::toDate($data,'FACTORY_DATE');
        self::toDate($data,'GET_DATE');
        self::toDate($data,'INPUT_TIME');
        Db::table(static::$table)->insert($data);
    }

    static public function addImport($data){
        $data['INPUT_PERSON'] = Login::getUserId();
        $data['INPUT_TIME'] = date('Y-m-d');

        self::toDate($data,'ENTER_TIME');
        self::toDate($data,'FACTORY_DATE');
        self::toDate($data,'GET_DATE');
        self::toDate($data,'INPUT_TIME');
        self::toDate($data,'FIXED_DATE');
        Db::table(static::$table)->insert($data);
    }

    static public function update($where,$data){
        self::toDate($data,'ENTER_TIME');
        self::toDate($data,'FACTORY_DATE');
        self::toDate($data,'GET_DATE');
        self::toDate($data,'FIXED_DATE');
        Db::table(static::$table)->where($where)->update($data);
    }

    static public function delete($where){
        return Db::table(static::$table)->where($where)->delete();
    }

    public static function toDate(&$data,$key){
        if(array_key_exists($key,$data)){
            $value = $data[$key];
            if(empty($value)){
                $data[$key] = null;
            }
            else{
                $data[$key] = Db::raw("to_date('$data[$key]','yyyy-mm-dd hh24:mi:ss')");
            }
        }
    }

    public static function getConfigPrint(){
        return Db::table(static::$configTable)->where(['PRINT_FLAG'=>'1'])->order('ID ASC')->select();
    }

    public static function getNextId(){
        $year = date('Y');
        $numMaxRecord = Db::table(static::$table)
            ->field('max(SUBSTR('.static::$pk.',10,6)) as NUM')
            ->where([['SUBSTR('.static::$pk.',6,4)','=',$year]])
            ->fetchSql(true)
            ->find();
        $numMaxRecord = Db::query($numMaxRecord);
        $num = $numMaxRecord[0]['NUM'];
        if ($num == NULL) {
            $num = 0;
        }
        $num++;
        return 'ZJ_JJ'.$year.sprintf("%06d", $num);
    }

    public static function exportExcel($data,$fileName){

        $fieldList = [
            ['title'=>'家具编号','field'=>'FURN_NO'],
            ['title'=>'所属在建工程编号','field'=>'CONS_NO'],
            ['title'=>'所属在建工程名称','field'=>'CONS_NAME']
            ,['title'=>'家具名称','field'=>'FURN_NAME']
            ,['title'=>'入账时间','field'=>'ENTER_TIME']
            ,['title'=>'分类号','field'=>'FURNTYPE_NO']
            ,['title'=>'分类名称','field'=>'FACITYPE']
            ,['title'=>'使用部门','field'=>'DEPART_SHORT']
            ,['title'=>'所在学院','field'=>'SHORTNAME']
            ,['title'=>'技术指标','field'=>'SPEC']
            ,['title'=>'型号','field'=>'MODEL']
            ,['title'=>'价格','field'=>'PRICE']
            ,['title'=>'厂商','field'=>'FACTORY']
            ,['title'=>'国别','field'=>'COUNTRY_CNNAME']
            ,['title'=>'出厂号','field'=>'FACTORY_NO']
            ,['title'=>'出厂日期','field'=>'FACTORY_DATE']
            ,['title'=>'购置日期','field'=>'GET_DATE']
            ,['title'=>'状态','field'=>'STATE']
            ,['title'=>'领用人','field'=>'GET_PERSON']
            ,['title'=>'经手人','field'=>'CHECK_PERSON']
            ,['title'=>'负责人','field'=>'CHARGE_PERSON']
            ,['title'=>'备注','field'=>'REMARK']
            ,['title'=>'制单人','field'=>'INPUT_NAME']
            ,['title'=>'制单时间','field'=>'INPUT_TIME']
            ,['title'=>'国标','field'=>'GB']
            ,['title'=>'国标名称','field'=>'GB_NAME']
            ,['title'=>'经费来源','field'=>'ZIJIN']
            ,['title'=>'来源明细','field'=>'ZJLY_DETAIL']
            ,['title'=>'隶属单位','field'=>'BELONG']
            ,['title'=>'国有资产分类号','field'=>'ASSET_TYPE_ID']
            ,['title'=>'国有资产分类','field'=>'ASSET_TYPE']
            ,['title'=>'品牌','field'=>'BRAND']
            ,['title'=>'存放地点','field'=>'PLACE']
            ,['title'=>'备注2','field'=>'REMARK2']
            ,['title'=>'发票号','field'=>'NOTICE_NO']
            ,['title'=>'是否批量采购','field'=>'MUTI_BUY_MARK']
            ,['title'=>'是否提交','field'=>'SUBMIT_MARK'],
            ['title'=>'是否转固','field'=>'IS_FIXED'],
            ['title'=>'转固资产编号','field'=>'FIXED_NO'],
            ['title'=>'转固日期','field'=>'FIXED_DATE'],
            ['title'=>'转固人','field'=>'FIXED_USER_NAME']
        ];

        $excel = new ExcelService();
        $excel->exportExcel($data,$fileName,$fieldList);
    }

    const FURN_FIELD_LIST = [
        "FURN_NO","CONS_NO","FURN_NAME","ENTER_TIME","FURNTYPE_NO","DEPART_ID","SPEC","MODEL","PRICE","FACTORY","COUNTRY_ID","FACTORY_NO","FACTORY_DATE","GET_DATE","STATE_ID","GET_PERSON","CHECK_PERSON","CHARGE_PERSON","REMARK","INPUT_PERSON","INPUT_TIME","GB","ZJLY","ZJLY_DETAIL","BELONG_ID","ASSET_TYPE_ID","ASSET_TYPE_ID","BRAND","PLACE","REMARK2","NOTICE_NO","MUTI_BUY_MARK","SUBMIT_MARK","IS_FIXED","FIXED_NO","FIXED_DATE","FIXED_USER_ID"
        ];

    public static function getSessionConditions() {
        $condition = null;
        $listCondition = Login::getListConditionConFurn();
        if (!empty($listCondition['FURN_NO'])) {
            $value = $listCondition['FURN_NO'];
            $condition[] = ['f.FURN_NO','like',"$value%"];
        }
        if (!empty($listCondition['FURN_NAME'])) {
            $value = $listCondition['FURN_NAME'];
            $condition[] = ['f.FURN_NAME','like',"%$value%"];
        }
        if (!empty($listCondition['FURNTYPE_NO'])) {
            $value = $listCondition['FURNTYPE_NO'];
            $condition[] = ['f.FURNTYPE_NO','=',$value];
        }
        if (!empty($listCondition['CONS_ID'])) {
            $value = $listCondition['CONS_ID'];
            $condition[] = ['f.CONS_ID','=',$value];
        }
        if (!empty($listCondition['ASSET_TYPE_ID'])) {
            $value = $listCondition['ASSET_TYPE_ID'];
            $condition[] = ['f.ASSET_TYPE_ID','=',$value];
        }
        if (!empty($listCondition['GB'])) {
            $value = $listCondition['GB'];
            $condition[] = ['f.GB','=',$value];
        }
        if (!empty($listCondition['STATE_ID'] || $listCondition['STATE_ID']==='00')) {
            $value = $listCondition['STATE_ID'];
            $condition[] = ['f.STATE_ID','=',$value];
        }
        if (!empty($listCondition['BELONG_ID']) || $listCondition['BELONG_ID']==='000') {
            $value = $listCondition['BELONG_ID'];
            $condition[] = ['f.BELONG_ID','=',$value];
        }
        if (!empty($listCondition['DEPART_ID'])) {
            $value = $listCondition['DEPART_ID'];
            $condition[] = ['f.DEPART_ID','=',$value];
        }
        if (!empty($listCondition['COUNTRY_ID'])) {
            $value = $listCondition['COUNTRY_ID'];
            $condition[] = ['f.COUNTRY_ID','=',$value];
        }
        if (!empty($listCondition['MUTI_BUY_MARK']) || $listCondition['MUTI_BUY_MARK']==='0') {
            $value = $listCondition['MUTI_BUY_MARK'];
            $condition[] = ['f.MUTI_BUY_MARK','=',$value];
        }
        if (!empty($listCondition['BRAND'])) {
            $value = $listCondition['BRAND'];
            $condition[] = ['f.BRAND','like',"%$value%"];
        }
        if (!empty($listCondition['MODEL'])) {
            $value = $listCondition['MODEL'];
            $condition[] = ['f.MODEL','like',"%$value%"];
        }
        if (!empty($listCondition['FACTORY'])) {
            $value = $listCondition['FACTORY'];
            $condition[] = ['f.FACTORY','like',"%$value%"];
        }
        if (!empty($listCondition['PRICE_START'])) {
            $value = $listCondition['PRICE_START'];
            $condition[] = ['f.PRICE','>=',$value];
        }
        if (!empty($listCondition['PRICE_END'])) {
            $value = $listCondition['PRICE_END'];
            $condition[] = ['f.PRICE','<=',$value];
        }
        if (!empty($listCondition['FACTORY_DATE_START'])) {
            $value = Db::raw("to_date('{$listCondition['FACTORY_DATE_START']}','yyyy-mm-dd')");
            $condition[] = ['f.FACTORY_DATE','>=',$value];
        }
        if (!empty($listCondition['FACTORY_DATE_END'])) {
            $value = Db::raw("to_date('{$listCondition['FACTORY_DATE_END']}','yyyy-mm-dd')");
            $condition[] = ['f.FACTORY_DATE','<=',$value];
        }
        if (!empty($listCondition['GET_DATE_START'])) {
            $value = Db::raw("to_date('{$listCondition['GET_DATE_START']}','yyyy-mm-dd')");
            $condition[] = ['f.GET_DATE','>=',$value];
        }
        if (!empty($listCondition['GET_DATE_END'])) {
            $value = Db::raw("to_date('{$listCondition['GET_DATE_END']}','yyyy-mm-dd')");
            $condition[] = ['f.GET_DATE','<=',$value];
        }
        if (!empty($listCondition['ENTER_TIME_START'])) {
            $value = Db::raw("to_date('{$listCondition['ENTER_TIME_START']}','yyyy-mm-dd')");
            $condition[] = ['f.ENTER_TIME','>=',$value];
        }
        if (!empty($listCondition['ENTER_TIME_END'])) {
            $value = Db::raw("to_date('{$listCondition['ENTER_TIME_END']}','yyyy-mm-dd')");
            $condition[] = ['f.ENTER_TIME','<=',$value];
        }
        return $condition;
    }

}